﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using EHSubmit.Model;
using EHSubmit.Util;
using EHSubmit.Logic.LINQ;


namespace EHSubmit.Logic
{
    /// <summary>
    ///GoalManager 的摘要说明     
    ///相关方法：获取发布的作业通知、作业通知被添加、作业通知被第一次删、作业通知被彻底删除、作业通知被更新等
    /// </summary>
    public class GoalManager
    {
        private const string SQL_SELECT_GOAL = "SELECT CourseID,TeacherID,Title,SubYear,Term,Description,AttachPath,FileTypeAllowed,PublishTime,DeadLine,LastSubmitTime,IsDeleted,GoalType FROM Goal WHERE GID = @GID AND IsDeleted = 0";
        private const string SQL_INSERT_GOAL = "INSERT INTO Goal(CourseID,TeacherID,Title,SubYear,Term,Description,AttachPath,FileTypeAllowed,PublishTime,DeadLine,LastSubmitTime,GoalType) VALUES(@CourseID,@TeacherID,@Title,@SubYear,@Term,@Description,@AttachPath,@FileTypeAllowed,@PublishTime,@DeadLine,@LastSubmitTime,@GoalType)";
        private const string SQL_INSERT_ObjGOAL = "INSERT INTO Goal(CourseID,TeacherID,Title,SubYear,Term,Description,AttachPath,FileTypeAllowed,PublishTime,DeadLine,LastSubmitTime,GoalType) VALUES(@CourseID,@TeacherID,@Title,@SubYear,@Term,@Description,@AttachPath,@FileTypeAllowed,@PublishTime,@DeadLine,@LastSubmitTime,@GoalType); SELECT @@IDENTITY AS goalID";
        private const string SQL_DELETE_FIRST = "UPDATE Goal SET IsDeleted = 1 WHERE GID = @GID";
        private const string SQL_DELETE_COMPELETELY = "DELETE Goal WHERE GID = @GID";
        private const string SQL_UPDATE_GOAL = "UPDATE Goal SET Course = @CourseID,TeacherID = @TeacherID,Title = @Title,Term = @Term,SubYear = @SubYear,Term =  @Term,Description = @Description,AttachPath = @AttachPath,FileTypeAllowed = @FileTypeAllowed,DeadLine = @DeadLine,LastSubmitTime = @LastSubmitTime,isDeleted = @isDeleted WHERE GID = @GID";

        //根据选择的课程班级id获取goals
        private const string SQL_SELECT_GOAL_OF_COURSE = "SELECT GID,CourseID,TeacherID,Title,SubYear,Term,Description,AttachPath,FileTypeAllowed,PublishTime,DeadLine,LastSubmitTime,IsDeleted,GoalType FROM Goal WHERE CourseID = @CourseID AND IsDeleted = 0";

        private const string PARM_ID = "@GID";                                     //作业ID
        private const string PARM_COURSEID = "@CourseID";                          //课程ID
        private const string PARM_TEACHERID = "@TeacherID";                        //教师ID
        private const string PARM_TITLE = "@Title";                                //标题
        private const string PARM_SUBYEAR = "@SubYear";                            //所在的学年
        private const string PARM_TERM = "@Term";                                  //所在的学期
        private const string PARM_DESCRIPTION = "@Description";                    //发布的作业描述
        private const string PARM_ATTACHPATH = "@AttachPath";                      //作业的附件路径
        private const string PARM_FILETYPEALLOWED = "@FileTypeAllowed";            //允许上传的文件类型
        private const string PARM_PUBLISHTIME = "@PublishTime";                    //发布时间
        private const string PARM_DEADLINE = "@DeadLine";                          //截止日期
        private const string PARM_LASTSUBMITTIME = "@LastSubmitTime";              //最近上交的作业更新时间
        private const string PARM_ISDELETED = "@IsDeleted";                        //是否删除           
        private const string PARM_GOALTYPE = "@GoalType";                          //作业类型？ 主观题，客观题

        /// <summary>
        /// 构造函数
        /// </summary>
        public GoalManager() { }


        /// <summary>
        /// 获取发布的某个作业通知的具体信息
        /// </summary>
        /// <param name="goalId">作业通知ID</param>
        /// <returns>作业通知的数据表</returns>
        public Goal GetGoal(int goalId)
        {
            try
            {
                if (goalId <= 0)
                {
                    throw new Exception("作业通知ID不正确");
                }
                else
                {
                    Goal goal = new Goal();
                    goal.ID = goalId;

                    SqlParameter parm = new SqlParameter(PARM_ID, SqlDbType.Int);
                    parm.Value = Convert.ToInt32(goalId);
                    using (SqlDataReader rdrGoal = (SqlDataReader)SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_GOAL, parm))
                    {
                        if (rdrGoal.Read())
                        {
                            goal.CourseID = rdrGoal["CourseID"] is DBNull ? string.Empty : (string)rdrGoal["CourseID"];                             //课程ID
                            goal.TeacherID = rdrGoal["TeacherID"] is DBNull ? string.Empty : (string)rdrGoal["TeacherID"];                          //教师ID
                            goal.Title = rdrGoal["Title"] is DBNull ? string.Empty : (string)rdrGoal["Title"];                                      //标题
                            goal.SubYear = rdrGoal["SubYear"] is DBNull ?string.Empty : (string)rdrGoal["SubYear"];                                 //所在的学年
                            goal.Term = rdrGoal["Term"] is DBNull ? string.Empty : (string)rdrGoal["Term"];                                         //所在的学期
                            goal.Description = rdrGoal["Description"] is DBNull ? string.Empty : (string)rdrGoal["Description"];                    //发布的作业描述
                            goal.AttachPath = rdrGoal["AttachPath"] is DBNull ? string.Empty : (string)rdrGoal["AttachPath"];                       //作业的附件路径
                            goal.FileTypeAllowed = rdrGoal["FileTypeAllowed"] is DBNull ? string.Empty : (string)rdrGoal["FileTypeAllowed"];        //允许上传的文件类型
                            goal.PublishTime = rdrGoal["PublishTime"] is DBNull ? DateTime.Now : (DateTime)rdrGoal["PublishTime"];                  //发布时间
                            goal.DeadLine = rdrGoal["DeadLine"] is DBNull ? DateTime.Now : (DateTime)rdrGoal["DeadLine"];                           //截止日期
                            goal.LastSubmitTime = rdrGoal["LastSubmitTime"] is DBNull ? DateTime.Now : (DateTime)rdrGoal["LastSubmitTime"];         //最近上交的作业更新时间
                            goal.GoalType = (Int32)rdrGoal["GoalType"];                                                                             //作业类型 
                            //string temp = rdrGoal["IsDeleted"].ToString();
                            goal.IsDeleted = Convert.ToByte(rdrGoal["IsDeleted"]) == 0 ? false : true;                                  //是否删除    
                        }
                    }
                    return goal;
                }
            }
            catch (Exception ex)
            {
                LogHelper.Log(ex.Message, ex);
                return null;
            }
        }

        /// <summary>
        /// 一条作业通知被添加（容错有待）
        /// </summary>
        /// <param name="Goal">作业通知</param>
        public void AddGoal(Goal goal)
        {

            int nResult;
            SqlParameter[] parms = new SqlParameter[12];
            parms[0] = new SqlParameter(PARM_COURSEID, SqlDbType.NChar, 10);                      //课程ID
            parms[1] = new SqlParameter(PARM_TEACHERID, SqlDbType.NChar, 6);                      //教师ID
            parms[2] = new SqlParameter(PARM_TITLE, SqlDbType.NVarChar, 100);                     //标题
            parms[3] = new SqlParameter(PARM_SUBYEAR, SqlDbType.NChar, 9);                        //所在的学年
            parms[4] = new SqlParameter(PARM_TERM, SqlDbType.NChar, 1);                           //所在的学期
            parms[5] = new SqlParameter(PARM_DESCRIPTION, SqlDbType.NVarChar, 200);               //发布的作业描述
            parms[6] = new SqlParameter(PARM_ATTACHPATH, SqlDbType.NVarChar, 100);                //作业的附件路径
            parms[7] = new SqlParameter(PARM_FILETYPEALLOWED, SqlDbType.NChar, 1);                //允许上传的文件类型
            parms[8] = new SqlParameter(PARM_PUBLISHTIME, SqlDbType.DateTime);                    //发布时间
            parms[9] = new SqlParameter(PARM_DEADLINE, SqlDbType.DateTime);                       //截止日期
            parms[10] = new SqlParameter(PARM_LASTSUBMITTIME, SqlDbType.DateTime);                //最近上交的作业更新时间
            parms[11] = new SqlParameter(PARM_GOALTYPE, SqlDbType.Int);                           //作业类型
            parms[0].Value = goal.CourseID;
            parms[1].Value = goal.TeacherID;
            parms[2].Value = goal.Title;
            parms[3].Value = goal.SubYear;
            parms[4].Value = goal.Term;
            parms[5].Value = goal.Description;
            parms[6].Value = goal.AttachPath;
            parms[7].Value = goal.FileTypeAllowed;
            parms[8].Value = Convert.ToDateTime(goal.PublishTime);
            parms[9].Value = Convert.ToDateTime(goal.DeadLine);
            parms[10].Value = Convert.ToDateTime(goal.LastSubmitTime);
            parms[11].Value = goal.GoalType;
            nResult = (int)SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_INSERT_GOAL, parms);
        }

        /// <summary>
        /// 布置客观题作业
        /// </summary>
        /// <param name="goal"></param>
        /// <param name="qidList"></param>
        public void AddObjGoal(Goal goal, IList<string> qidList)
        {

            SqlParameter[] parms = new SqlParameter[12];
            parms[0] = new SqlParameter(PARM_COURSEID, SqlDbType.NChar, 10);                      //课程ID
            parms[1] = new SqlParameter(PARM_TEACHERID, SqlDbType.NChar, 6);                      //教师ID
            parms[2] = new SqlParameter(PARM_TITLE, SqlDbType.NVarChar, 100);                     //标题
            parms[3] = new SqlParameter(PARM_SUBYEAR, SqlDbType.NChar, 9);                        //所在的学年
            parms[4] = new SqlParameter(PARM_TERM, SqlDbType.NChar, 1);                           //所在的学期
            parms[5] = new SqlParameter(PARM_DESCRIPTION, SqlDbType.NVarChar, 200);               //发布的作业描述
            parms[6] = new SqlParameter(PARM_ATTACHPATH, SqlDbType.NVarChar, 100);                //作业的附件路径
            parms[7] = new SqlParameter(PARM_FILETYPEALLOWED, SqlDbType.NChar, 1);                //允许上传的文件类型
            parms[8] = new SqlParameter(PARM_PUBLISHTIME, SqlDbType.DateTime);                    //发布时间
            parms[9] = new SqlParameter(PARM_DEADLINE, SqlDbType.DateTime);                       //截止日期
            parms[10] = new SqlParameter(PARM_LASTSUBMITTIME, SqlDbType.DateTime);                //最近上交的作业更新时间
            parms[11] = new SqlParameter(PARM_GOALTYPE, SqlDbType.Int);                           //作业类型
            parms[0].Value = goal.CourseID;
            parms[1].Value = goal.TeacherID;
            parms[2].Value = goal.Title;
            parms[3].Value = goal.SubYear;
            parms[4].Value = goal.Term;
            parms[5].Value = goal.Description;
            parms[6].Value = goal.AttachPath;
            parms[7].Value = goal.FileTypeAllowed;
            parms[8].Value = Convert.ToDateTime(goal.PublishTime);
            parms[9].Value = Convert.ToDateTime(goal.DeadLine);
            parms[10].Value = Convert.ToDateTime(goal.LastSubmitTime);
            parms[11].Value = goal.GoalType;

            //--获取插入的Goal的id
            Int32 goalID = -1;
            using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_INSERT_ObjGOAL, parms))
            {
                if (rdr.Read())
                {
                    goalID = Convert.ToInt32(rdr["goalID"]);                                             
                }
            }

            //----处理Goal和Question的关系
            using (EHSubmitDataContext db = new EHSubmitDataContext(SqlHelper.ConnectionStringLocalTransaction))
            {
                IList<GoalQuestion> gqList = new List<GoalQuestion>();
                foreach (string qid in qidList)
                {
                    GoalQuestion goalQuestion = new GoalQuestion();
                    goalQuestion.GoalID = goalID;
                    goalQuestion.QuestionID = Convert.ToInt32(qid);
                    goalQuestion.RightCount = 0;
                    goalQuestion.WrongCount = 0;
                    gqList.Add(goalQuestion);
                }

                db.GoalQuestion.InsertAllOnSubmit(gqList);
                db.SubmitChanges();
            }
        }


        /// <summary>
        /// 作业通知被第一次删（单个）
        /// </summary>
        /// <param name="goal">作业通知</param>
        public void DeleteGoal(Goal goal)                             //第一次删除
        {
            try
            {
                if (goal.ID == 0)
                {
                    throw new Exception("作业通知ID不能为空");
                }
                else
                {
                    SqlParameter parm = new SqlParameter(PARM_ID, SqlDbType.Int);
                    int nResult;
                    parm.Value = Convert.ToInt32(goal.ID);
                    nResult = (int)SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_DELETE_FIRST, parm);
                }
            }
            catch (Exception ex)
            {
                LogHelper.Log(ex.Message, ex);
            }
        }


        /// <summary>
        /// 作业通知被第一次删（批量）
        /// </summary>
        /// <param name="goals">作业通知</param>
        public void DeleteGoal(Goal[] goals)
        {
            for (int i = 0; i < goals.Length; i++)
            {
                DeleteGoal(goals[i]);
            }
        }


        /// <summary>
        /// 作业通知被彻底删除（单个）
        /// </summary>
        /// <param name="goal">作业通知</param>
        public void CompletelyDeleteGoal(Goal goal)
        {
            try
            {
                if (goal.ID == 0)
                {
                    throw new Exception("作业通知ID不能为空");
                }
                else
                {
                    SqlParameter parm = new SqlParameter(PARM_ID, SqlDbType.Int);                     //作业ID
                    int nResult;
                    parm.Value = Convert.ToInt32(goal.ID);
                    nResult = (int)SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_DELETE_COMPELETELY, parm);
                }
            }
            catch (Exception ex)
            {
                LogHelper.Log(ex.Message, ex);
            }
        }

        /// <summary>
        /// 作业通知被彻底删除（批量）
        /// </summary>
        /// <param name="goals">作业通知</param>
        public void CompletelyDeleteGoal(Goal[] goals)
        {
            for (int i = 0; i < goals.Length; i++)
            {
                CompletelyDeleteGoal(goals[i]);
            }
        }

        /// <summary>
        /// 一条作业通知被更新（直接对对象操作）
        /// </summary>
        public void UpdateGoal(Goal goal)
        {
            try
            {
                if (goal.ID == 0)
                {
                    throw new Exception("作业通知ID不能为空");
                }
                else
                {
                    int nResult;
                    SqlParameter[] parms = new SqlParameter[12];
                    parms[0] = new SqlParameter(PARM_COURSEID, SqlDbType.NChar, 10);                      //课程ID
                    parms[1] = new SqlParameter(PARM_TEACHERID, SqlDbType.NChar, 6);                      //教师ID
                    parms[2] = new SqlParameter(PARM_TITLE, SqlDbType.NVarChar, 100);                     //标题
                    parms[3] = new SqlParameter(PARM_SUBYEAR, SqlDbType.NChar, 9);                        //所在的学年
                    parms[4] = new SqlParameter(PARM_TERM, SqlDbType.NChar, 1);                           //所在的学期
                    parms[5] = new SqlParameter(PARM_DESCRIPTION, SqlDbType.NVarChar, 200);               //发布的作业描述
                    parms[6] = new SqlParameter(PARM_ATTACHPATH, SqlDbType.NVarChar, 100);                //作业的附件路径
                    parms[7] = new SqlParameter(PARM_FILETYPEALLOWED, SqlDbType.NChar, 1);                 //允许上传的文件类型
                    parms[8] = new SqlParameter(PARM_PUBLISHTIME, SqlDbType.DateTime);                    //发布时间
                    parms[9] = new SqlParameter(PARM_DEADLINE, SqlDbType.DateTime);                       //截止日期
                    parms[10] = new SqlParameter(PARM_LASTSUBMITTIME, SqlDbType.DateTime);                //最近上交的作业更新时间
                    parms[11] = new SqlParameter(PARM_ISDELETED, SqlDbType.Bit);                           //是否删除           
                    parms[0].Value = goal.CourseID;
                    parms[1].Value = goal.TeacherID;
                    parms[2].Value = goal.Title;
                    parms[3].Value = goal.SubYear;
                    parms[4].Value = goal.Term;
                    parms[5].Value = goal.Description;
                    parms[6].Value = goal.AttachPath;
                    parms[7].Value = goal.FileTypeAllowed;
                    parms[8].Value = Convert.ToDateTime(goal.PublishTime);
                    parms[9].Value = Convert.ToDateTime(goal.DeadLine);
                    parms[10].Value = Convert.ToDateTime(goal.LastSubmitTime);
                    parms[11].Value = goal.IsDeleted;
                    nResult = (int)SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_UPDATE_GOAL, parms);
                }
            }
            catch (Exception ex)
            {
                LogHelper.Log(ex.Message, ex);
                
            }
        }


        /*--------------------------------------------------------------------------------------------
          string tbName ,                                             -- 表名
          string fieldsToReturn = '*',                                -- 需要返回的列    如列1，列2，...
          string fieldToOrder ='',                                    -- 排序的字段名    如表1 (不用加[])  
          int pageSize = 10,                                          -- 页尺寸
          int pageIndex = 1,                                          -- 页码
          bool doCount = false;                                       -- 返回记录总数, 非 0 值则返回
          bool IsDesc = true,                                         -- 设置排序类型, 非 0 值则降序
          string criteria = ''                                        -- 查询条件 (注意: 不要加 where ,如果是多条件，要将其作为一个整体传入，如（a=1 or b=2）外围的括号不可少)
         --------------------------------------------------------------------------------------------*/
        /// <summary>
        /// 获取单页的作业通知集合    默认记录数为10  
        /// </summary>
        /// <param name="tid">教师ID</param>
        /// <param name="pageIndex">当前页的页码</param>
        /// <returns>作业通知集合</returns>
        public IList<Goal> GetGoals(string tid, int pageIndex)
        {
            PagedDataProvider pagedDataProvider = new PagedDataProvider();
            pagedDataProvider.TbName = "Goal";
            pagedDataProvider.FieldsToReturn = "*";
            pagedDataProvider.FieldToOrder = "PublishTime";
            pagedDataProvider.PageSize = 10;           //默认记录数为10
            pagedDataProvider.PageIndex = pageIndex;
            pagedDataProvider.DoCount = false;
            pagedDataProvider.IsDesc = true;
            //pagedDataProvider.Criteria = "TeacherID = " + tid + " AND IsDeleted = 0";
            SqlDataReader rdrGoals = pagedDataProvider.GetResults();
            IList<Goal> goalList = new List<Goal>();
            try
            {
                while (rdrGoals.Read())
                {
                    Goal goal = new Goal();
                    //for (int i = 0; i < rdrGoals.FieldCount - 1; i++)
                    //{
                    //    if (!(rdrGoals[i] is DBNull))
                    //    {
                    //        //C.C 可以解决空值转换会出错的问题，但属性名要与数据库中的相应字段名一致，还有要删掉flag字段
                    //        ObjectHelper.SetAttr(goal,rdrGoals.GetName(i),rdrGoals.GetValue(i));
                    //    }
                    //}

                    goal.ID = (Int32)rdrGoals["GID"];                                                                                              //作业ID
                    goal.CourseID = rdrGoals["CourseID"] is DBNull ? string.Empty : (string)rdrGoals["CourseID"];                                  //课程ID
                    goal.TeacherID = rdrGoals["TeacherID"] is DBNull ? string.Empty : (string)rdrGoals["TeacherID"];                               //教师ID
                    goal.Title = rdrGoals["Title"] is DBNull ? string.Empty : (string)rdrGoals["Title"];                                           //标题
                    goal.SubYear = rdrGoals["SubYear"] is DBNull ? string.Empty : (string)rdrGoals["SubYear"];                                     //所在的学年
                    goal.Term = rdrGoals["Term"] is DBNull ? string.Empty : (string)rdrGoals["Term"];                                              //所在的学期
                    goal.Description = rdrGoals["Description"] is DBNull ? string.Empty : (string)rdrGoals["Description"];                         //发布的作业描述
                    goal.AttachPath = rdrGoals["AttachPath"] is DBNull ? string.Empty : (string)rdrGoals["AttachPath"];                            //作业的附件路径  
                    goal.FileTypeAllowed = rdrGoals["FileTypeAllowed"] is DBNull ? string.Empty : (string)rdrGoals["FileTypeAllowed"];             //允许上传的文件类型
                    goal.PublishTime = rdrGoals["PublishTime"] is DBNull ? DateTime.Now : (DateTime)rdrGoals["PublishTime"];                       //发布时间
                    goal.DeadLine = rdrGoals["DeadLine"] is DBNull ? DateTime.Now : (DateTime)rdrGoals["DeadLine"];                                //截止日期
                    goal.LastSubmitTime = rdrGoals["LastSubmitTime"] is DBNull ? DateTime.Now : (DateTime)rdrGoals["LastSubmitTime"];              //最近上交的作业更新时间
                    goal.IsDeleted = Convert.ToByte(rdrGoals["IsDeleted"]) == 0 ? false : true;                                        //是否删除  
                    goal.GoalType = (Int32)rdrGoals["GoalType"];

                    goalList.Add(goal);           
                }
            }
            finally
            {
                if (rdrGoals != null)   //关闭与数据库连接
                    rdrGoals.Close();
            }
            return goalList;
        }


        /*--------------------------------------------------------------------------------------------
            string tbName ,                                             -- 表名
            bool doCount = false;                                       -- 返回记录总数, 非 0 值则返回
            string criteria = ''                                        -- 查询条件 (注意: 不要加 where ,如果是多条件，要将其作为一个整体传入，如（a=1 or b=2）外围的括号不可少)
          --------------------------------------------------------------------------------------------*/
        /// <summary>
        /// 获取作业通知总数量
        /// </summary>
        /// <param name="tid">教师ID</param>
        /// <returns>作业通知数量</returns>
        public int GetGoalPageCount(string tid)
        {
            PagedDataProvider pagedDataProvider = new PagedDataProvider();
            pagedDataProvider.TbName = "Goal";
            pagedDataProvider.DoCount = true;
            //pagedDataProvider.Criteria = "TeacherID = " + tid + " AND IsDeleted = 0";
            pagedDataProvider.TotalCount = pagedDataProvider.GetRecordCount();
            if (pagedDataProvider.TotalCount == -1)
            {
                //有待处理解决
                return -1;
            }
            else
            {
                return pagedDataProvider.TotalCount; 
            }
        }


        /// <summary>
        /// 暂用aspnetpager控件测试分页
        /// </summary>
        /// <returns>作业通知</returns>
        public SqlDataReader GetGoalsTest(string tid, int pageIndex)
        {
            PagedDataProvider pagedDataProvider = new PagedDataProvider();
            pagedDataProvider.TbName = "Goal";
            pagedDataProvider.FieldsToReturn = "*";
            pagedDataProvider.FieldToOrder = "PublishTime";
            pagedDataProvider.PageSize = 8;
            pagedDataProvider.PageIndex = pageIndex;
            pagedDataProvider.DoCount = false;
            pagedDataProvider.IsDesc = true;
            pagedDataProvider.Criteria = "TeacherID = " + tid + " AND IsDeleted = 0";
            SqlDataReader rdrGoals = pagedDataProvider.GetResults();
            return rdrGoals;
        }


        /// <summary>
        /// 根据选择的课程班级id获取goals
        /// </summary>
        /// <param name="courseID">课程班级id</param>
        /// <returns>goals信息</returns>
        public IList<Goal> GetGoalsOfCourse(string courseID)
        {
            IList<Goal> goals = new List<Goal>();
            SqlParameter[] parms = new SqlParameter[1];
            parms[0] = new SqlParameter(PARM_COURSEID, SqlDbType.NChar, 10);                      //课程ID
            parms[0].Value = courseID;

            using (SqlDataReader rdrGoals = (SqlDataReader)SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_GOAL_OF_COURSE, parms))
            {
                while (rdrGoals.Read())
                {
                    Goal goal = new Goal();
                    goal.ID = rdrGoals["GID"] is DBNull ? -1:(int)rdrGoals["GID"];      
                    goal.CourseID = rdrGoals["CourseID"] is DBNull ? string.Empty : (string)rdrGoals["CourseID"];                             //课程ID
                    goal.TeacherID = rdrGoals["TeacherID"] is DBNull ? string.Empty : (string)rdrGoals["TeacherID"];                          //教师ID
                    goal.Title = rdrGoals["Title"] is DBNull ? string.Empty : (string)rdrGoals["Title"];                                      //标题
                    goal.SubYear = rdrGoals["SubYear"] is DBNull ? string.Empty : (string)rdrGoals["SubYear"];                                 //所在的学年
                    goal.Term = rdrGoals["Term"] is DBNull ? string.Empty : (string)rdrGoals["Term"];                                         //所在的学期
                    goal.Description = rdrGoals["Description"] is DBNull ? string.Empty : (string)rdrGoals["Description"];                    //发布的作业描述
                    goal.AttachPath = rdrGoals["AttachPath"] is DBNull ? string.Empty : (string)rdrGoals["AttachPath"];                       //作业的附件路径
                    goal.FileTypeAllowed = rdrGoals["FileTypeAllowed"] is DBNull ? string.Empty : (string)rdrGoals["FileTypeAllowed"];        //允许上传的文件类型
                    goal.PublishTime = rdrGoals["PublishTime"] is DBNull ? DateTime.Now : (DateTime)rdrGoals["PublishTime"];                  //发布时间
                    goal.DeadLine = rdrGoals["DeadLine"] is DBNull ? DateTime.Now : (DateTime)rdrGoals["DeadLine"];                           //截止日期
                    goal.LastSubmitTime = rdrGoals["LastSubmitTime"] is DBNull ? DateTime.Now : (DateTime)rdrGoals["LastSubmitTime"];         //最近上交的作业更新时间
                    goal.IsDeleted = Convert.ToByte(rdrGoals["IsDeleted"]) == 0 ? false : true;                                               //是否删除    
                    goal.GoalType = (Int32)rdrGoals["GoalType"];                                                                              //作业类型
                    goals.Add(goal);
                }
                return goals;
            }
        }

        /// <summary>
        /// 根据选课信息获得所选课的作业
        /// </summary>
        /// <param name="courses">课程信息</param>
        /// <returns>作业信息</returns>
        public IList<Goal> GetGoalsOfCourses(IList<Course> courses, IList<Homework> works)
        {
            string sql = CreateGoalsOfCoursesSQL(courses, works);

            IList<Goal> goals = new List<Goal>();
          
            using (SqlDataReader rdrGoals = (SqlDataReader)SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sql, null))
            {
                while (rdrGoals.Read())
                {
                    Goal goal = new Goal();
                    goal.ID = (Int32)rdrGoals["GID"];                                                                                              //作业ID
                    goal.CourseID = rdrGoals["CourseID"] is DBNull ? string.Empty : (string)rdrGoals["CourseID"];                                  //课程ID
                    goal.TeacherID = rdrGoals["TeacherID"] is DBNull ? string.Empty : (string)rdrGoals["TeacherID"];                               //教师ID
                    goal.Title = rdrGoals["Title"] is DBNull ? string.Empty : (string)rdrGoals["Title"];                                           //标题
                    goal.SubYear = rdrGoals["SubYear"] is DBNull ? string.Empty : (string)rdrGoals["SubYear"];                                     //所在的学年
                    goal.Term = rdrGoals["Term"] is DBNull ? string.Empty : (string)rdrGoals["Term"];                                              //所在的学期
                    goal.Description = rdrGoals["Description"] is DBNull ? string.Empty : (string)rdrGoals["Description"];                         //发布的作业描述
                    goal.AttachPath = rdrGoals["AttachPath"] is DBNull ? string.Empty : (string)rdrGoals["AttachPath"];                            //作业的附件路径  
                    goal.FileTypeAllowed = rdrGoals["FileTypeAllowed"] is DBNull ? string.Empty : (string)rdrGoals["FileTypeAllowed"];             //允许上传的文件类型
                    goal.PublishTime = rdrGoals["PublishTime"] is DBNull ? DateTime.Now : (DateTime)rdrGoals["PublishTime"];                       //发布时间
                    goal.DeadLine = rdrGoals["DeadLine"] is DBNull ? DateTime.Now : (DateTime)rdrGoals["DeadLine"];                                //截止日期
                    goal.LastSubmitTime = rdrGoals["LastSubmitTime"] is DBNull ? DateTime.Now : (DateTime)rdrGoals["LastSubmitTime"];              //最近上交的作业更新时间
                    goal.IsDeleted = Convert.ToByte(rdrGoals["IsDeleted"]) == 0 ? false : true;                                                    //是否删除  
                    goal.GoalType = (Int32)rdrGoals["GoalType"];                                                                                   //作业类型
                    
                    goals.Add(goal);
                }
            }

            return goals;
        }

        /// <summary>
        /// 产生根据课程查找相应作业信息的SQL语句(未被提交过)
        /// </summary>
        /// <param name="courses">课程信息集合</param>
        /// <returns>SQL语句</returns>
        private string CreateGoalsOfCoursesSQL(IList<Course> courses, IList<Homework> works)
        {
            string sqlTemplate = "SELECT * FROM Goal WHERE {0} CourseID IN ({1})";
            string sqlCondition = "GID NOT IN ({0}) AND";
            int curr = 0;
            int count = 0;


            

            count = works.Count;
            curr = 0;

            // 如果已交过作业,则查询将这些筛选掉
            if (count > 0)
            {
                StringBuilder condtion_works = new StringBuilder();
                foreach (Homework w in works)
                {

                    condtion_works.Append("'");
                    condtion_works.Append(w.GoalID);
                    condtion_works.Append("'");

                    curr++;

                    if (curr < count)
                    {
                        condtion_works.Append(",");
                    }

                }

                sqlCondition = string.Format(sqlCondition, condtion_works.ToString());
            }
            else
            {
                sqlCondition = string.Empty;
            }

            
            StringBuilder condtion_course = new StringBuilder();

            count = courses.Count;
            curr = 0;
            foreach (Course c in courses)
            {

                condtion_course.Append("'");
                condtion_course.Append(c.Id);
                condtion_course.Append("'");

                curr++;

                if (curr < count)
                {
                    condtion_course.Append(",");
                }

            }

            

            // 最终查询的SQL语句
            string sql = string.Format(sqlTemplate, sqlCondition, condtion_course.ToString());

            return sql;
        }

        /// <summary>
        /// 根据选课信息获得所选课的作业通知(有提交，但未评分)
        /// </summary>
        /// <param name="courses">课程信息</param>
        /// <returns>作业信息</returns>
        public IList<Goal> GetGoalsUnmarkedOfCourses(IList<Course> courses, IList<Homework> works)
        {
            string sql = CreateGoalsUnmarkedOfCoursesSQL(courses, works);
            IList<Goal> goals = new List<Goal>();

            if (sql == null)
            {
                return goals;
            }
            using (SqlDataReader rdrGoals = (SqlDataReader)SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sql, null))
            {
                while (rdrGoals.Read())
                {
                    Goal goal = new Goal();
                    goal.ID = (Int32)rdrGoals["GID"];                                                                                              //作业ID
                    goal.CourseID = rdrGoals["CourseID"] is DBNull ? string.Empty : (string)rdrGoals["CourseID"];                                  //课程ID
                    goal.TeacherID = rdrGoals["TeacherID"] is DBNull ? string.Empty : (string)rdrGoals["TeacherID"];                               //教师ID
                    goal.Title = rdrGoals["Title"] is DBNull ? string.Empty : (string)rdrGoals["Title"];                                           //标题
                    goal.SubYear = rdrGoals["SubYear"] is DBNull ? string.Empty : (string)rdrGoals["SubYear"];                                     //所在的学年
                    goal.Term = rdrGoals["Term"] is DBNull ? string.Empty : (string)rdrGoals["Term"];                                              //所在的学期
                    goal.Description = rdrGoals["Description"] is DBNull ? string.Empty : (string)rdrGoals["Description"];                         //发布的作业描述
                    goal.AttachPath = rdrGoals["AttachPath"] is DBNull ? string.Empty : (string)rdrGoals["AttachPath"];                            //作业的附件路径  
                    goal.FileTypeAllowed = rdrGoals["FileTypeAllowed"] is DBNull ? string.Empty : (string)rdrGoals["FileTypeAllowed"];             //允许上传的文件类型
                    goal.PublishTime = rdrGoals["PublishTime"] is DBNull ? DateTime.Now : (DateTime)rdrGoals["PublishTime"];                       //发布时间
                    goal.DeadLine = rdrGoals["DeadLine"] is DBNull ? DateTime.Now : (DateTime)rdrGoals["DeadLine"];                                //截止日期
                    goal.LastSubmitTime = rdrGoals["LastSubmitTime"] is DBNull ? DateTime.Now : (DateTime)rdrGoals["LastSubmitTime"];              //最近上交的作业更新时间
                    goal.IsDeleted = rdrGoals["IsDeleted"] is DBNull ? false : (bool)rdrGoals["IsDeleted"];                                        //是否删除  
                    goal.GoalType = (Int32)rdrGoals["GoalType"];                                                                                   //作业类型

                    goals.Add(goal);
                }
            }

            return goals;
        }

        /// <summary>
        /// 产生根据课程查找相应作业通知的SQL语句(有提交，但未评分)
        /// </summary>
        /// <param name="courses">课程信息集合</param>
        /// <returns>SQL语句</returns>
        private string CreateGoalsUnmarkedOfCoursesSQL(IList<Course> courses, IList<Homework> works)
        {
            string sqlTemplate = "SELECT * FROM Goal WHERE {0} CourseID IN ({1})";
            string sqlCondition = "GID IN ({0}) AND";
            int curr = 0;
            int count = 0;

            count = works.Count;
            curr = 0;

            // 如果已交过作业,则查询将这些筛选掉
            if (count > 0)
            {
                StringBuilder condtion_works = new StringBuilder();
                foreach (Homework w in works)
                {
                    //--若作业已经评分，则过滤
                    if(w.Scores == -1)
                    {
                        condtion_works.Append("'");
                        condtion_works.Append(w.GoalID);
                        condtion_works.Append("'");
                        condtion_works.Append(",");
                    }
                }

                if (condtion_works.Length != 0)
                {
                    //---去除最后面多余的逗号
                    condtion_works.Remove(condtion_works.Length - 1, 1);
                }
                //---若所交的作业都已评分
                else
                {
                    return null;
                }

                sqlCondition = string.Format(sqlCondition, condtion_works.ToString());
            }
            //--如果未交过作业，则返回null
            else
            {
                return null;
            }

            
            StringBuilder condtion_course = new StringBuilder();

            count = courses.Count;
            curr = 0;
            foreach (Course c in courses)
            {

                condtion_course.Append("'");
                condtion_course.Append(c.Id);
                condtion_course.Append("'");

                curr++;

                if (curr < count)
                {
                    condtion_course.Append(",");
                }

            }

            // 最终查询的SQL语句
            string sql = string.Format(sqlTemplate, sqlCondition, condtion_course.ToString());

            return sql;
        }

    }
}
